import { Inject, Provide } from '@midwayjs/core';
import { query, transaction } from '../dbConnPool/mariadb'; // 引入查询函数
import { BigIntService } from '../service/bigInt.service';
import { Context } from '@midwayjs/koa';
import { StatisticsDTO } from '../dto/statistics';
import { Caching } from '@midwayjs/cache-manager';
@Provide()
export class StatisticsDao {
  @Inject()
  ctx: Context;
  @Inject()
  bigIntService: BigIntService;
  // 获取统计数据
  async getStatistics(statisticsDTO: StatisticsDTO) {
    const { startDate, endDate } = statisticsDTO;
    return transaction(async conn => {
      const sql = `
        SELECT 
            date,
            new_users AS newUsers,
            new_patients AS newPatients,
            page_views AS pageViews
        FROM daily_statistics
        WHERE date >= ? AND date <= ?
    `;
      const result = await query(sql, [startDate, endDate], false, conn);
      const sql2 = `
        SELECT COUNT(*) FROM user WHERE valid_flag = 1;
        `;
      const totalUsers = await query(sql2, [], false, conn);
      const sql3 = `
        SELECT COUNT(*) FROM patient WHERE valid_flag = 1;
        `;
      const totalPatients = await query(sql3, [], false, conn);
      const sql4 = `
        SELECT SUM(page_views) AS totalPageViews FROM daily_statistics;
        `;
      const totalPageViews = await query(sql4, [], false, conn);

      return {
        data: result,
        totals: {
          totalUsers: totalUsers[0]['COUNT(*)'],
          totalPatients: totalPatients[0]['COUNT(*)'],
          totalPageViews: totalPageViews[0]['totalPageViews'],
        },
      };
    });
  }
  //获取科室统计信息
  @Caching('redis', ctx => {
    if (ctx.methodArgs.length > 0 && ctx.methodArgs[0]) {
      return `DepartmentStatistics:${ctx.methodArgs[0]}`;
    }
    return null;
  })
  async getDepartmentStatistics(
    departmentId: string,
    statisticsDTO: StatisticsDTO
  ) {
    const { startDate, endDate } = statisticsDTO;
    return transaction(async conn => {
      const sql = `
        SELECT 
            date,
            department_id AS departmentId,
            new_patients AS newPatients,
            new_diagnoseds AS newDiagnoseds,
            new_records AS newRecords
            FROM department_daily_statistics
        WHERE date >= ? AND date <= ? AND department_id = ?
    `;
      const result = await query(
        sql,
        [startDate, endDate, departmentId],
        false,
        conn
      );
      const sql2 = `
        SELECT COUNT(*) FROM patient WHERE department_id = ? AND valid_flag = 1;
        `;
      const totalPatients = await query(sql2, [departmentId], false, conn);
      const sql3 = `
        SELECT SUM(new_diagnoseds) AS totalDiagnoseds FROM department_daily_statistics WHERE department_id = ?;
        `;
      const totalDiagnoseds = await query(sql3, [departmentId], false, conn);
      const sql4 = `
        SELECT COUNT(*) FROM record WHERE department_id = ? AND valid_flag = 1;
        `;
      const totalRecords = await query(sql4, [departmentId], false, conn);
      return this.bigIntService.bigInt({
        data: result,
        totals: {
          totalPatients: totalPatients[0]['COUNT(*)'],
          totalDiagnoseds: totalDiagnoseds[0]['totalDiagnoseds'],
          totalRecords: totalRecords[0]['COUNT(*)'],
        },
      });
    });
  }
}
